#!python
"""
walk through all directories. find all  xls files 
convert all the xls files to xlsx 
2020/04/30  不拷贝隐藏Sheet | first try excel then wps.et
2020/04/19: 加入 拷贝时 在第一列 写入文件名的功能 | 关闭 excel 对话框
                excelApp.DisplayAlerts = False
2020/04/09: 加入callback // 递归找目录 // 按 sheet copy
2018/02/27: 开始一个一个单元格的拷贝，速度太慢
            后面使用内置的range（范围）拷贝速度大大提升

"""

import os
import debug
import win32com.client as win32

OUTPUTFILE = '汇总表.xlsx'
starRow = 1
starRowOfSht = {}

def copyExcel_bysheets(wb1, wb2, sheets, first=False, fname=None): # wb1 -> wb2 
    """
        选择特定的sheet,只合并同名sheet
        wb1 被拷贝的workbook
        wb2 目的地workbook
        sheets  {} key:表名 value:标题行
    """
    global starRowOfSht

    for sh in wb1.Worksheets:
        shtname = sh.Name
        if shtname not in sheets: # 不是选定的就跳过
            debug.prt(" Skip>> %s" % shtname)
            continue  # 
        if first == True: # 如果设置了header ,则更新header
            headers = 0
        else:
            headers = sheets[shtname]
        
        try:
            dest_sh = wb2.Worksheets[shtname]
        except Exception as e:
            debug.err(" no sheet name[%s] SO adding..." % shtname)
            dest_sh = wb2.Worksheets.Add()
            dest_sh.Name = shtname
            starRowOfSht[shtname] = 1

        starRow = starRowOfSht[shtname]
        max_row = int(sh.UsedRange.Address.split("$")[-1])
        rowscount = sh.UsedRange.Rows.Count
        max_col = sh.UsedRange.Columns.Count

        if max_row <= headers or rowscount == 0: #没有数据 跳过
            continue
        debug.prt("[%s  - StarRow] >> %d  maxrow %d" % (shtname, starRow, max_row))
        #--------------------------------------------------
        rangeObj = sh.Range(sh.Cells(1+headers,1), sh.Cells(max_row, max_col))
        if fname == None:
            rangeObj.Copy(dest_sh.Cells(starRow, 1))
        else:
            rangeObj.Copy(dest_sh.Cells(starRow, 2))
            dest_sh.Cells(starRow,1).Value = fname
        #--------------------------------------------------
        starRowOfSht[shtname] += max_row - headers 

def copyExcelTo(wb1, wb2, headers=0, fname=None): # wb1 -> wb2 
    """
        全部拷贝到一个sht上
        wb1 被拷贝的workbook
        wb2 目的地workbook
        headers 标题行数  默认为0 
        fname 被复制的文件名
    """
    global starRow

    dest_sh = wb2.ActiveSheet
 
    for sh in wb1.Worksheets:
        if sh.Visible >= 0: # not copy the hided sheet 0 Hidden -1 visible 2 veryhidden
            continue
        max_row = int(sh.UsedRange.Address.split("$")[-1])
        rowscount = sh.UsedRange.Rows.Count
        max_col = sh.UsedRange.Columns.Count

        if max_row <= headers or rowscount == 0: #没有数据 跳过
            continue
        debug.prt("[StarRow] >> %d  maxrow %d" % (starRow,max_row))
        rangeObj = sh.Range(sh.Cells(1+headers,1), sh.Cells(max_row, max_col))
        if fname == None:
            rangeObj.Copy(dest_sh.Cells(starRow, 1))
        else:
            rangeObj.Copy(dest_sh.Cells(starRow, 2))
            dest_sh.Cells(starRow,1).Value = fname

        
        starRow += max_row - headers 
        

def mergeExcel(fileList, destDir='.', cb=None, headers=0, sheets=None, save_fname=False):
    """
        fileList  待合并的文件列表
        destDir   合并结果存放目录
        cb        回调函数
        headers   标题行数
        sheets    {} key:表名 value:标题行数
        save_fname 是否在第一列保存列名
    """
    try:
        global starRow
        global starRowOfSht
        appstr = "excel.Application et.Application".split()

        try:
            excelApp = win32.gencache.EnsureDispatch(appstr[0])
        except:
            debug.log("excel open failed--try wps")
            excelApp = win32.gencache.EnsureDispatch(appstr[1])

        excelApp.Visible = False
        excelApp.DisplayAlerts = False
        merge_wb = excelApp.Workbooks.Add()
        first_copy = True 
        starRow = 1  # reset to 1
        starRowOfSht = {}
        ret = (True, "")

        for fname in fileList:
            debug.prt('[采集] >> %s ' % fname)
            s_fname = os.path.basename(fname) if save_fname == True else None
            err = None
            try:
                wb = excelApp.Workbooks.Open(fname)
                if first_copy:
                    if sheets != None:
                        copyExcel_bysheets(wb, merge_wb, sheets, True, fname=s_fname)
                    else:
                        copyExcelTo(wb, merge_wb, fname=s_fname)
                    first_copy = False
                else:
                    if sheets != None:
                        copyExcel_bysheets(wb, merge_wb, sheets, fname=s_fname)
                    else:
                        copyExcelTo(wb, merge_wb, headers, fname=s_fname)

                wb.Close()
            except Exception as e:
                debug.err("merge >> 打开文件失败 %s" % fname)
                err= {}
                err["msg"] = "[Err]打开文件失败 %s \n" % os.path.basename(fname)

            if cb != None: # call the callback 
                cb(fname)
                cb(None,err)


        debug.prt('Done.')
        savepath = os.path.abspath(os.path.join(destDir,OUTPUTFILE))
        merge_wb.SaveAs(savepath,51) # 51 --- xlWorkbookDefault
        merge_wb.Close(True)
        debug.prt("save rusult>> [%s]" % savepath)
        ret = (True, savepath)

    except Exception as e:
        debug.err(" mergeExcel > %s " %  str(e))
        ret =  (False, str(e))

    finally:
        if excelApp != None:
            excelApp.Application.Quit()
        debug.prt("ExcelApp quit!!!!")
        return  ret


def find_excel_by_dir(d):
    '''
        d  目标目录
        去目录中搜索 excel表格文件，返回文件路径的列表
    '''

    if os.path.isdir(d) == True:
        fileList = []
        debug.prt('find all the xls files.. in [%s]' % d)
        for filename in os.listdir(d):
            if filename == OUTPUTFILE:
                continue
            if not filename.endswith('.xls') and \
                    not filename.endswith('.xlsx'): # skip non-xls files
                continue
            filepath = os.path.abspath(os.path.join(d,filename))
            fileList.append(filepath)
            #fileList.append(os.path.abspath(os.path.join(folder,filename)))
        debug.prt('Find %d xls files' % len(fileList))
        return fileList

def find_excel_by_dir_re(d):
    """ 
        d  
        遍历目录下所有文件夹
    """
    if os.path.isdir(d) == True:
        fileList = []
        debug.prt('find all the xls files.. [recrusive] in [%s]' % d)
        for folder, subFolders, filenames in os.walk(d):
            debug.prt("enter dir: %s ..." % subFolders)
            for filename in filenames:
                if filename == OUTPUTFILE:
                    continue
                if not filename.endswith('.xls') and \
                        not filename.endswith('.xlsx'): # skip non-xls files
                    continue
                filepath = os.path.abspath(os.path.join(folder,filename))
                fileList.append(filepath)

        debug.prt('Find %d xls files' % len(fileList))
        return fileList


if __name__ == "__main__":
    #test = {"表1政府性财政收支情况总表":10, "表2资产负债表":5}
    l = find_excel_by_dir('./testdata')
    #mergeExcel(l, headers=1, sheets=test)
    mergeExcel(l)

    #for f in l:
    #    print("Result>> ",f)
    #input('Done!!!')